package com.xjt.excel.util;



import com.googlecode.aviator.AviatorEvaluator;
import com.xjt.excel.entity.PubImpExcelModelcol;
import com.xjt.excel.entity.PubImpExcelValidLog;
import com.xjt.excel.service.impl.PubImpExcelValidLogServiceImpl;
import com.xjt.excel.service.impl.PubImpExcelValidRuleServiceImpl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @author xujiangtao
 * @version 1.0
 */
public class ReadExcelUtil {

    //总条数
    private static int totalCells = 0;
    //构造方法
    public ReadExcelUtil(){}


    public static PubImpExcelValidLogServiceImpl pubImpExcelValidLogService;


    public static PubImpExcelValidRuleServiceImpl pubImpExcelValidRuleService;

    public static void  setPubImpExcelValidLogServiceImpl(PubImpExcelValidLogServiceImpl pubImpExcelValidLogServices){
        pubImpExcelValidLogService = pubImpExcelValidLogServices;
    }

    public static void  setPubImpExcelValidRuleServiceImpl(PubImpExcelValidRuleServiceImpl pubImpExcelValidRuleServices){
        pubImpExcelValidRuleService = pubImpExcelValidRuleServices;
    }

    /**
     * 读EXCEL文件，获取信息集合
     * @return 数据
     */
    public static Map<String,Object> getExcelInfo(String mFile, List<PubImpExcelModelcol> ModelcolList,Long pubImpExcelJdLogId,Map<Integer,String> res,int sheetIndex) throws Exception {
        String fileName = new File(mFile).getName();//获取文件名
        if (!validateExcel(fileName)) {// 验证文件名是否合格
            return null;
        }
        boolean isExcel2003 = !isExcel2007(fileName);// 根据文件名判断文件是2003版本还是2007版本
        return createExcel(new FileInputStream(mFile), isExcel2003,ModelcolList,pubImpExcelJdLogId,res,sheetIndex);
    }


    /**
     * 根据excel里面的内容读取客户信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return 数据
     */
    public static Map<String,Object> createExcel(InputStream is, boolean isExcel2003,List<PubImpExcelModelcol> ModelcolList,Long pubImpExcelJdLogId,Map<Integer,String> res,int sheetIndex) throws Exception {
        Workbook wb;
        if (isExcel2003) {// 当excel是2003时,创建excel2003
            wb = new HSSFWorkbook(is);
        } else {// 当excel是2007时,创建excel2007
            wb = new XSSFWorkbook(is);
        }
        return readExcelValue(wb,ModelcolList,pubImpExcelJdLogId,res,sheetIndex);
    }

    /**
     * 读取表头信息
     * @param file 文件
     * @return 数据
     */
    public static Map<Integer,String> readExcelhead(MultipartFile file,int sheetIndex) throws Exception {
        FileInputStream in = (FileInputStream) file.getInputStream();
        Workbook wb;
        if (isExcel2003(Objects.requireNonNull(file.getOriginalFilename()))){
            wb = new HSSFWorkbook(in);
        }else {
            wb = new XSSFWorkbook(in);
        }
        Map<Integer, String> map = new HashMap<>();
        Sheet sheet = wb.getSheetAt(sheetIndex);
        int headCol=0;
        if (sheet.getRow(headCol) != null && sheet.getPhysicalNumberOfRows()>1){
            while (isMergedRegion(sheet, headCol, 0)){
                headCol++;
            }
        }
        int colSum = sheet.getRow(headCol).getPhysicalNumberOfCells();
        for (int i = 0; i < colSum; i++) {
            Cell cell = sheet.getRow(headCol).getCell(i);
            if (null != cell) {
                String stringCellValue = cell.getStringCellValue();
                map.put(i, stringCellValue);
            }
        }
        return map;
    }


    /**
     * 读取Excel里面数据的信息
     * @param wb 工作簿对象
     * @return 数据
     */
    private static Map<String,Object> readExcelValue(Workbook wb,List<PubImpExcelModelcol> ModelcolList,Long pubImpExcelJdLogId,Map<Integer,String> res,int sheetIndex) {
        Map<String,Object> map = new HashMap<>();
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(sheetIndex);
        String sheetName = sheet.getSheetName();
        //存储表头所在行的下一行
        int headCol=0;
        if (sheet.getRow(headCol) != null && sheet.getPhysicalNumberOfRows()>1){
            while (isMergedRegion(sheet, headCol, 0)){
                headCol++;
            }
        }
        // 得到Excel的行数
        //总行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            if (totalCells==1){
                totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
            }
        }
        List<PubImpExcelValidLog> impExcelValidLogList = new ArrayList<>();
        List<List<String>> lists = new ArrayList<>();
        // 循环Excel行数，从headCol开始遍历数据
        int fail = 0;
        for (int r = headCol+1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            // 循环Excel的列
            List<String> list = new ArrayList<>();
            int validRes = 1;
            String validTip = "";
            list.add((r+1)+"");   //行号
            list.add(validRes+"");
            list.add(validTip);
            for (int c = 0; c < totalCells; c++) {
                String validateZz = ModelcolList.get(c).getValidateZz();            //数据校验正则表达式
                Cell cell = row.getCell(c);
                Integer isReq = ModelcolList.get(c).getIsReq();
                if (isReq==1 && (cell==null || getCellValue(cell).equals(""))){
                    if (validTip.equals("")){
                        validTip = "("+c+")"+res.get(c)+":该单元格为必填项";
                    }else {
                        validTip = validTip+"。"+"("+c+")"+res.get(c)+":该单元格为必填项";
                    }
                    validRes = 2;
                }
                if (null != cell) {
                    if (isMergedRegion(sheet, r, c)){
                        String mergedRegionValue = getMergedRegionValue(sheet, r, c);
                        list.add(mergedRegionValue);
                    }else {
                        if (cell.getCellType() != CellType.STRING && org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell))
                        {
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                            Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                            String value = sdf.format(date);
                            List<String> ids = StrUtil.subString(validateZz, "[", "]");
                            List<Map<String, Object>> ruleByIds = pubImpExcelValidRuleService.getRuleByIds(ids);
                            for (Map<String, Object> ruleById : ruleByIds) {
                                String valid_expression = ruleById.get("valid_expression").toString();
                                boolean matchess = value.matches(valid_expression);
                                if (!matchess) {
                                    if (validTip.equals("")) {
                                        validTip = "(" + c + ")" + res.get(c) + ":" + ruleById.get("valid_tip").toString();
                                    } else {
                                        validTip = validTip + "。" + "(" + c + ")" + res.get(c) + ":" + ruleById.get("valid_tip").toString();
                                    }
                                }
                                String bool = String.valueOf(matchess);
                                String regex = "[" + ruleById.get("valid_code").toString() + "]";
                                validateZz = validateZz.replace(regex, bool);
                            }
                            boolean matches2 = (boolean) AviatorEvaluator.execute(validateZz);
                            if (validRes == 1){
                                validRes = matches2?1:2;
                            }
                            list.add(value);

                            if (!matches2){
                                Long R = (long) r;
                                Long C = (long) c;
                                impExcelValidLogList.add(new PubImpExcelValidLog(C, validTip, pubImpExcelJdLogId, R, sheetName));
                            }
                        }else {
                            String stringCellValue = getCellValue(cell);
                            List<String> ids = StrUtil.subString(validateZz, "[", "]");
                            List<Map<String, Object>> ruleByIds = pubImpExcelValidRuleService.getRuleByIds(ids);
                            for (Map<String, Object> ruleById : ruleByIds) {
                                String valid_expression = ruleById.get("valid_expression").toString();
                                boolean matches = stringCellValue.matches(valid_expression);
                                if (!matches) {
                                    if (validTip.equals("")) {
                                        validTip = "(" + c + ")" + res.get(c) + ":" + ruleById.get("valid_tip").toString();
                                    } else {
                                        validTip = validTip + "。" + "(" + c + ")" + res.get(c) + ":" + ruleById.get("valid_tip").toString();
                                    }
                                }
                                String bool = String.valueOf(matches);
                                String regex = "[" + ruleById.get("valid_code").toString() + "]";
                                validateZz = validateZz.replace(regex, bool);
                            }
                            boolean matches = (boolean) AviatorEvaluator.execute(validateZz);
                            if (validRes == 1){
                                validRes = matches?1:2;
                            }
                            if (stringCellValue.equals("now()")){
                                DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                                list.add(LocalDateTime.now().format(df));
                            }else {
                                list.add(stringCellValue);
                            }

                            if (!matches){
                                Long R = (long) r;
                                Long C = (long) c;
                                impExcelValidLogList.add(new PubImpExcelValidLog(C, validTip, pubImpExcelJdLogId, R, sheetName));
                            }
                        }
                    }
                }else {
                    list.add("null");
                }
            }
            StringBuilder rowString = new StringBuilder();
            for (String s : list) {
                rowString.append(s);
            }
            boolean flag = !rowString.toString().equals("");
            String valid = String.valueOf(validRes);
            list.set(1, valid);
            if ("1".equals(valid)){
                list.set(2, "通过");
            }else {
                list.set(2, validTip);
            }
            if (list.size()>3 && flag){
                lists.add(list);
            }
            if (validRes == 2){
                fail++;
            }
        }
        if (impExcelValidLogList.size()>1){
            pubImpExcelValidLogService.saveBatch(impExcelValidLogList);
        }
        map.put("list", lists);
        map.put("fail", fail);
        return map;
    }
    /**
     * 验证EXCEL文件
     *
     * @param filePath 文件路径
     * @return 是否为excel文件
     */
    public static boolean validateExcel(String filePath) {
        return filePath != null && (isExcel2003(filePath) || isExcel2007(filePath));
    }
    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }
    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 获取合并单元格的值
     * @param sheet sheet页
     * @param row 所在行
     * @param column 所在列
     * @return 单元格数据
     */
    public static String getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellValue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet sheet页
     * @param row 所在行
     * @param column 所在列
     * @return 是否合并单元格
     */
    public static boolean isMergedRegion(Sheet sheet , int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++ ){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){

                    return true ;
                }
            }
        }

        return false ;
    }

    /**
     * 获取单元格的值
     * @param cell 单元格对象
     * @return 单元格数据
     */
    public static String getCellValue(Cell cell){

        if(cell == null) {
            return "";
        }

        if(cell.getCellType() == CellType.STRING){

            return cell.getStringCellValue();

        }else if(cell.getCellType() == CellType.BOOLEAN){

            return String.valueOf(cell.getBooleanCellValue());

        }else if(cell.getCellType() == CellType.FORMULA){

            return cell.getCellFormula() ;

        }else if(cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
            return String.valueOf(cell.getStringCellValue());

        }

        return "";
    }
}
